<?php 
include_once 'excel_head.php'; 

if (isset($_POST['fd'] , $_POST['fh'])) {
    $fechaDesde = $_POST['fd']; 
    $fechaHasta = $_POST['fh']; 
} else {
    // The correct POST variables were not sent to this page. 
    header('Location: main.php?pag=ro');
}

function empresaSesion() {
	return $_SESSION['empresa'];
}

// New Connection
$db = new mysqli(PHPGRID_DB_HOSTNAME, PHPGRID_DB_USERNAME, PHPGRID_DB_PASSWORD, PHPGRID_DB_NAME);

// Check for errors
if(mysqli_connect_errno()){
 echo mysqli_connect_error();
}

$reporte->setActiveSheetIndex(0)->setCellValue("F2","PRODUCTIVIDAD CONSOLIDADA POR OPERARIOS"); 
$reporte->setActiveSheetIndex(0)->setCellValue("F3","Fecha Desde: ".$fechaDesde." - Fecha Hasta: ".$fechaHasta); 

$result = $db->query("SELECT ID_OPERARIO, NOMBRE FROM serverinstall.operarios WHERE EMPRESA ='".empresaSesion()."'");

if($result){
     // Cycle through results
    while ($row = $result->fetch_array()){
			$operarios[] = $row;
    }
    // Free result set
    $result->close();
    //$db->next_result();
}

$fila = 9;

foreach ($operarios as $op) {

			$reporte->setActiveSheetIndex(0)->setCellValue("B$fila","Operario"); 
			$reporte->setActiveSheetIndex(0)->setCellValue("C$fila",$op['NOMBRE']);  

			$fila = $fila + 2;

			$reporte->setActiveSheetIndex(0)->setCellValue("B$fila","Cod Items"); 
			$reporte->setActiveSheetIndex(0)->setCellValue("C$fila","Item"); 
			$reporte->setActiveSheetIndex(0)->setCellValue("D$fila","Cantidad"); 
			$reporte->setActiveSheetIndex(0)->setCellValue("E$fila","Facturado"); 
			$reporte->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
			$reporte->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
			$reporte->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
			$reporte->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);

			for ( $i = 1 ; $i < 5 ; $i ++) {
			
				$reporte->getActiveSheet()
				    ->getStyleByColumnAndRow($i, $fila)
				    ->getFont()->applyFromArray($style['label']);
				
				$reporte->getActiveSheet()
				    ->getStyleByColumnAndRow($i, $fila)
						->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
				
				$reporte->getActiveSheet()
				    ->getStyleByColumnAndRow($i, $fila)
						->getFill()->getStartColor()->setRGB('E4EAF4');
			
			}

			$fila = $fila + 1;
			$result = $db->query("CALL `serverinstall`.`facturacionOperarios`('".$fechaDesde."', '".$fechaHasta."', ".$op['ID_OPERARIO'].",".empresaSesion().")");
			if($result){
			     // Cycle through results
			    while ($row = $result->fetch_object()){
						$reporte->setActiveSheetIndex(0)->setCellValue("B$fila",$row->CODIGO_ITEM);  
						$reporte->setActiveSheetIndex(0)->setCellValue("C$fila",utf8_encode($row->DESC_ITEM));  
						$reporte->setActiveSheetIndex(0)->setCellValue("D$fila",$row->CANTIDAD);  
						$reporte->setActiveSheetIndex(0)->setCellValue("E$fila",$row->FACTURADO);  
						$fila = $fila + 1;
			    }
			     // Free result set
			     $result->close();
			     $db->next_result();
			}
			else echo($db->error);

			$fila = $fila + 1;

}

// Close connection
$db->close();

$objWriter = PHPExcel_IOFactory::createWriter($reporte, 'Excel2007'); 

//ob_end_clean();

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="facturacion_operarios.xlsx"');
header('Cache-Control: max-age=0'); 	

$objWriter->save('php://output');
?>